<?php
require_once ('utils' . DIRECTORY_SEPARATOR . 'includes' . DIRECTORY_SEPARATOR . 'simple_html_dom.php');
require_once ('utils' . DIRECTORY_SEPARATOR . 'HHAbstract.php');

set_time_limit(-1);

class HHStore extends HHAbstract {

	const URL = 'http://www.hudhomestore.com/';
	public $local;

	function __construct() {
		parent::__construct();
	}

	function __destruct() {
		parent::__destruct();
	}

	function constructClass() {
		$this -> local = 'LOCAL';
		$this -> handleARGV();
	}

	function handleARGV() {
		global $argv;
		if (!isset($argv) || count($argv) <= 1) {
			$this -> update();

		}
		else {
			array_shift($argv);
			foreach ($argv as $value) {
				$keyArray = split("=", $value);
				$key = $keyArray[0];
				$keyValue = $keyArray[1];
				switch ($key) {
					case "state" :
						$state = $keyValue;
						if (isset($state)) {
							$this -> updateState($state);
						}
						break;
					default :
						echo("Incorrect argument usage. Please use one of the following:\n");
					case "options" :
						$options = array(
							"options" => "Display the options list",
							"state" => "Update the DB with data for the given state. i.e. state=MD"
						);
						$space = 0;
						foreach (array_keys ( $options ) as $opKey) {
							$space = (strlen($opKey) > $space) ? strlen($opKey) : $space;
						}
						$space += 5;
						$spaceString = '';
						for ($i = 0; $i < $space; $i++) {
							$spaceString .= " ";
						}
						$length = 70;
						foreach ($options as $opKey => $opValue) {
							$lineCount = 0;
							$displayArray = array();
							$displayArray[$lineCount] = ("\n$opKey" . substr($spaceString, strlen($opKey)));
							$opValueArray = explode(" ", $opValue);
							foreach ($opValueArray as $opValueWord) {
								if ((strlen($displayArray[$lineCount]) + strlen($opValueWord)) <= $length) {
									$displayArray[$lineCount] .= $opValueWord . " ";
								}
								else {
									$lineCount++;
									$displayArray[$lineCount] .= $spaceString . $opValueWord . " ";
								}
							}
							echo(implode("\n", $displayArray) . "\n");
						}
						break;
				}
			}
		}
	}

	function updateState($state) {
		$success = $this -> loadListResults($state);
		$success = ($success && $this -> loadBidResults($state));
		if ($success == true) {
			$updateQuery = "UPDATE states SET updated_at=now() where abbreviation='" . $state . "'";
			$this -> getDBConnection() -> queryDB($updateQuery);
			echo($state . " updated | Time: " . date("m-d-y h:i:s A") . "<br>");
		}
		else {
			echo($state . " was not fully updated | Time: " . date("m-d-y h:i:s A") . "<br>");
		}
	}

	function update() {
		echo("<hr>Updating Hud Property List and Bids. | Time: " . date("m-d-y h:i:s A") . "<br>");
		$query = "SELECT abbreviation AS 'state' FROM states 
		ORDER BY updated_at DESC, rand()";
		// Select all ordered by when they were last updated
		$results = $this -> getDBConnection() -> queryDB($query);
		$className = get_class($this);
		$file = $className . ".txt";
		while (($row = $results -> fetch_assoc())) {
			$state = $row["state"];
			$cmd = $className . ".php state=$state";
			$this -> getCommandLineHelper() -> run_in_background($cmd, $file);
		}
		
		// Schedule the geocoding for the new listings
		$this -> getCommandLineHelper() -> run_in_background('HHSGeoCodeDB.php', 'HHSGeoCodeDB.txt');
	}

	function get_web_page($url) {
		// This example request includes an optional API key which you will need to
		// remove or replace with your own key.
		// Read more about why it's useful to have an API key.
		// The request also includes the userip parameter which provides the end
		// user's IP address. Doing so will help distinguish this legitimate
		// server-side traffic from traffic which doesn't come from an end-user.
		// sendRequest
		// note how referer is set manually
		$options = array(
			CURLOPT_URL => $url,
			CURLOPT_RETURNTRANSFER => true, // return web page
			CURLOPT_FOLLOWLOCATION => true, // follow redirects
			CURLOPT_ENCODING => "", // handle all encodings
			CURLOPT_REFERER => "www.hudhomestore.com", // set referer on redirect
			CURLOPT_CONNECTTIMEOUT => 240, // timeout on connect
			CURLOPT_TIMEOUT => 240, // timeout on response
			CURLOPT_MAXREDIRS => 20,
			CURLOPT_USERAGENT => 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1) Gecko/20061204 Firefox/4',
			CURLOPT_BUFFERSIZE => (1024 * 1024 * 512)
		);
		// stop after 10 redirects
		$ch = curl_init();
		curl_setopt_array($ch, $options);

		$body = curl_exec($ch);
		curl_close($ch);

		return $body;
	}

	function loadListResults($state, $county = '', $city = '', $zipcode = '', $street = '', $caseNumber = '', $fromPrice = '', $toPrice = '', $bed = '', $bath = '', $buyerType = '', $status = '', $pageSize = 5000, $pageId = 1) {

		$success = true;
		try {
			$urlParams = array(
				'zipCode' => $zipcode,
				'city' => $city,
				'county' => $county,
				'street' => $street,
				'sState' => $state,
				'fcaseNumber' => $caseNumber,
				'fromPrice' => $fromPrice,
				'toPrice' => $toPrice,
				'bed' => $bed,
				'bath' => $bath,
				'buyerType' => $buyerType,
				'Status' => $status,
				'sLanguage' => 'ENGLISH',
				//'pageId' => $pageId,
				//'sPageSize' => $pageSize
			);

			$url = self::URL . 'pages/ListExportToExcel.aspx?' . http_build_query($urlParams);

			//echo('URL: ' . $url . '<br><br>');

			$hudhomestore = $this -> get_web_page($url);

			$html = str_get_html($hudhomestore);

			if ($html === false) {
				echo("<b><font color='red'>Could not process URL: <a href='$url'>" . $url . "</a><br>Consider increasing the MAX_FILE_SIZE in simple_html_dom.php or make sure the url actually returns a page.</font></b>");
				return false;
			}

			$tableTr = $html -> find('table tr');

			$csvTable = array();

			foreach ($tableTr as $tr) {
				$trArray = array();
				foreach ($tr->find('td') as $td) {
					$text = $td -> plaintext;
					if (count($trArray) == 13 || count($trArray) == 14) {
						$trArray[] = date('Y-m-d', strtotime(trim($text)));
					}
					else {
						$trArray[] = trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($text));
					}
				}
				$csvTable[] = implode(',', $trArray);
			}
			//var_dump($csvTable);

			// Write file to tmp location
			$lineTerminater = '\n';
			$path = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'tmp';
			$fileName = $path . DIRECTORY_SEPARATOR . "propertyList_" . $state . ".csv";
			file_put_contents($fileName, implode("\r\n", $csvTable));
			chmod(dirname($fileName), 0755);

			$tableName = 'property_list';

			// Create temporary table
			$tempTableName = $tableName . '_tmp_' . strtolower($state);
			$createTempQuery = 'CREATE TEMPORARY TABLE ' . $tempTableName . ' LIKE ' . $tableName;
			//$createTempQuery = 'CREATE TABLE IF NOT EXISTS ' . $tempTableName . ' LIKE ' . $tableName;
			$this -> getDBConnection() -> queryDB($createTempQuery);

			// Drop all the indexes in the temp table
			$showIndexQuery = "SHOW INDEX FROM " . $tempTableName;
			$results = $this -> getDBConnection() -> queryDB($showIndexQuery);
			while (($row = $results -> fetch_assoc())) {
				$indexName = $row['Key_name'];
				$dropIndexQuery = 'DROP INDEX `' . $indexName . '` ON ' . $tempTableName;
				$this -> getDBConnection() -> queryDB($dropIndexQuery);
			}

			// Load the CSV into the temporary table
			//$loadsql = 'LOAD DATA LOW_PRIORITY ' . $this -> local . ' INFILE "' . $this -> getDBConnection() -> getDBConnection() -> real_escape_string($fileName) . '" REPLACE INTO TABLE ' . $tempTableName . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'"\' LINES TERMINATED BY "' . $lineTerminater . '"';
			$loadsql = 'LOAD DATA ' . $this -> local . ' INFILE "' . $this -> getDBConnection() -> getDBConnection() -> real_escape_string($fileName) . '" INTO TABLE ' . $tempTableName . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'"\' LINES TERMINATED BY "' . $lineTerminater . '"';
			$loadsql .= ' IGNORE 1 LINES';
			//die($loadsql);
			$this -> getDBConnection() -> queryDB($loadsql);

			// Get all the table fields except lat on lon to use for On Duplicate key update
			$columnsQuery = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 			WHERE table_name = \'' . $tableName . '\'
 			AND COLUMN_NAME != \'lat\' AND COLUMN_NAME != \'lon\'';
			$results = $this -> getDBConnection() -> queryDB($columnsQuery);
			$columnCopyMap = array();
			while (($row = $results -> fetch_assoc())) {
				if(strcasecmp('case', $row['COLUMN_NAME'])==0){
					$row['COLUMN_NAME'] = '`'.$row['COLUMN_NAME'].'`';
				}
				$columnCopyMap[] =   $row['COLUMN_NAME'] . ' = ttc.' . $row['COLUMN_NAME'];
			}

			// Copy the data using ON DUPLICATE KEY UPDATE
			$copyQuery = 'INSERT INTO ' . $tableName . ' 
			SELECT * FROM ' . $tempTableName . ' AS ttc
			ON DUPLICATE KEY UPDATE ' . implode(', ', $columnCopyMap);
			$this -> getDBConnection() -> queryDB($copyQuery);
	
			// Remove the temporary table
			$removeTempQuery = 'DROP TEMPORARY TABLE ' . $tempTableName;
			$this -> getDBConnection() -> queryDB($removeTempQuery);

		}
		catch(Exception $e) {
			$success = false;
		}
		return $success;

	}

	function loadBidResults($state, $county = '', $city = '', $zipcode = '', $street = '', $caseNumber = '', $pageSize = 5000, $pageId = 1) {

		$success = true;
		try {
			$urlParams = array(
				'zipCode' => $zipcode,
				'city' => $city,
				'county' => $county,
				'street' => $street,
				'sState' => $state,
				'caseNumber' => $caseNumber,
				'pageId' => $pageId,
				'sPageSize' => $pageSize,
				'OrderbyName' => 'SCASENUMBER',
				'OrderbyValue' => 'ASC',
				'sLanguage' => 'ENGLISH'
			);

			$url = self::URL . 'Listing/BidResults.aspx?' . http_build_query($urlParams);

			$hudhomestore = $this -> get_web_page($url);

			$html = str_get_html($hudhomestore . ' ');

			if ($html === false) {
				echo("<b><font color='red'>Could not process URL: <a href='$url'>" . $url . "</a><br>Consider increasing the MAX_FILE_SIZE in simple_html_dom.php or make sure the url actually returns a page.</font></b>");
				return false;
			}

			$tableTr = $html -> find('table[id=dgBidList] tr');

			$csvTable = array();

			foreach ($tableTr as $tr) {
				$trArray = array();
				foreach ($tr->find('td') as $td) {
					$text = $td -> plaintext;
					if (count($csvTable) == 0) {
						$text = str_ireplace(array(
							"\r\n",
							"\r",
							"\n"
						), ' ', $text);
					}
					if (count($trArray) == 1) {
						$address = $text;
						$addressParts = explode("\r\n", $address);
						$addressParts[] = '';
						// add entry in case address is short
						$addressParts[] = '';
						// add entry in case address is short
						list($address, $cityStateZip, $county) = $addressParts;
						$cityStateZipParts = explode(',', $cityStateZip);
						$cityStateZipParts[] = '';
						// add entry in case address is short
						$cityStateZipParts[] = '';
						// add entry in case address is short
						list($city, $csvstate, $zip) = $cityStateZipParts;
						$trArray[] = '"' . trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($address)) . '"';
						$trArray[] = '"' . trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($city)) . '"';
						$trArray[] = '"' . trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($csvstate)) . '"';
						$trArray[] = '"' . trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($zip)) . '"';
						$trArray[] = '"' . trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($county)) . '"';
					}
					else
					if (count($trArray) == 6) {
						$trArray[] = str_replace(array(
							'$',
							','
						), '', trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($text))); ;
					}
					else
					if (count($trArray) >= 8 && count($trArray) < 11) {
						$trArray[] = date('Y-m-d', strtotime(trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($text))));
					}
					else {
						$trArray[] = trim($this -> getDBConnection() -> getDBConnection() -> real_escape_string($text));
					}
				}
				$csvTable[] = implode(',', $trArray);
			}
			//var_dump($csvTable);

			// Write file to tmp location
			$lineTerminater = '\n';
			$path = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'tmp';
			$fileName = $path . DIRECTORY_SEPARATOR . "bidResults_" . $state . ".csv";
			file_put_contents($fileName, implode("\r\n", $csvTable));
			chmod(dirname($fileName), 0755);

			$tableName = 'bid_results';

			// Create temporary table
			$tempTableName = $tableName . '_tmp_' . strtolower($state);
			$createTempQuery = 'CREATE TEMPORARY TABLE ' . $tempTableName . ' LIKE ' . $tableName;
			$this -> getDBConnection() -> queryDB($createTempQuery);

			// Drop all the indexes in the temp table
			$showIndexQuery = "SHOW INDEX FROM " . $tempTableName;
			$results = $this -> getDBConnection() -> queryDB($showIndexQuery);
			while (($row = $results -> fetch_assoc())) {
				$indexName = $row['Key_name'];
				$dropIndexQuery = 'DROP INDEX `' . $indexName . '` ON ' . $tempTableName;
				$this -> getDBConnection() -> queryDB($dropIndexQuery);
			}

			// Load the CSV into the temporary table
			//$loadsql = 'LOAD DATA LOW_PRIORITY ' . $this -> local . ' INFILE "' . $this -> getDBConnection() -> getDBConnection() -> real_escape_string($fileName) . '" REPLACE INTO TABLE ' . $tempTableName . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'"\' LINES TERMINATED BY "' . $lineTerminater . '"';
			$loadsql = 'LOAD DATA ' . $this -> local . ' INFILE "' . $this -> getDBConnection() -> getDBConnection() -> real_escape_string($fileName) . '" INTO TABLE ' . $tempTableName . ' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'"\' LINES TERMINATED BY "' . $lineTerminater . '"';
			$loadsql .= ' IGNORE 1 LINES';
			//die($loadsql);
			$this -> getDBConnection() -> queryDB($loadsql);

			// Get all the table fields except lat on lon to use for On Duplicate key update
			$columnsQuery = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
	 		WHERE table_name = \'' . $tableName . '\'
	 		AND COLUMN_NAME != \'lat\' AND COLUMN_NAME != \'lon\'';
			$results = $this -> getDBConnection() -> queryDB($columnsQuery);
			$columnCopyMap = array();
			while (($row = $results -> fetch_assoc())) {
				if(strcasecmp('case', $row['COLUMN_NAME'])==0){
					$row['COLUMN_NAME'] = '`'.$row['COLUMN_NAME'].'`';
				}
				$columnCopyMap[] = $row['COLUMN_NAME'] . ' = ttc.' . $row['COLUMN_NAME'];
			}

			// Copy the data using ON DUPLICATE KEY UPDATE
			$copyQuery = 'INSERT INTO ' . $tableName . ' 
			SELECT * FROM ' . $tempTableName . ' AS ttc
			ON DUPLICATE KEY UPDATE ' . implode(', ', $columnCopyMap);
			$this -> getDBConnection() -> queryDB($copyQuery);

			// Remove the temporary table
			$removeTempQuery = 'DROP TEMPORARY TABLE ' . $tempTableName;
			$this -> getDBConnection() -> queryDB($removeTempQuery);

		}
		catch(Exception $e) {
			$success = false;
		}
		return $success;
	}

}

$obj = new HHStore();
//$obj -> updateState('TX');
//$obj -> updateState('GA');
//$obj -> updateState('CO');
?>